package me.zhengjie.util;

import lombok.extern.slf4j.Slf4j;
import me.zhengjie.pojo.sqlServer.PPStorage;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

@Slf4j
@Component
public class ExcelUtil {

    private static final Pattern xlsx = Pattern.compile(".*[.]xlsx");
    private static final Pattern xls = Pattern.compile(".*[.]xls");

    public static List<PPStorage> readPPStorageFromExcel(InputStream inputStream, String originalFilename){

        Workbook workbook;
        List<PPStorage> res = new ArrayList<>();

        if (originalFilename == null) {
            return res;
        }

        try {
            if (xls.matcher(originalFilename).matches()) {
                workbook = new HSSFWorkbook(inputStream);
            } else if (xlsx.matcher(originalFilename).matches()) {
                workbook = new XSSFWorkbook(inputStream);
            } else {
                return res;
            }

            Sheet sheet = workbook.getSheetAt(0);

            if (sheet == null) {
                return res;
            }

            int lastRowNum = sheet.getLastRowNum();
            int firstRowNum = sheet.getFirstRowNum();

            Row title = sheet.getRow(firstRowNum);
            int lastCellNum = title.getLastCellNum();
            int firstCellNum = title.getFirstCellNum();

            int logicalAddressTypeColum = 0;
            int warehouseCodeColum = 0;
            int palletCodeColum = 0;
            int zoneCodeColum = 0;
            int storageCodeColum = 0;
            int aisleColum = 0;
            int layerColum = 0;
            int rowColum = 0;
            int columnColum = 0;
            int locationInventoryStatusColum = 0;
            int statusColum = 0;

            for (int i = firstCellNum; i < lastCellNum; i++) {
                Cell cell = title.getCell(i);
                if (cell == null) {
                    continue;
                }

                if (cell.getCellType() != CellType.STRING) {
                    continue;
                }

                String value = cell.getStringCellValue();
                if (value.equalsIgnoreCase("逻辑地址类型")) {
                    logicalAddressTypeColum = i;
                } else if (value.equalsIgnoreCase("仓库编码(PP，PTL)")) {
                    warehouseCodeColum = i;
                } else if (value.equalsIgnoreCase("储位编码")) {
                    storageCodeColum = i;
                } else if (value.equalsIgnoreCase("库区编码")) {
                    zoneCodeColum = i;
                } else if(value.equalsIgnoreCase("载盘编码")){
                    palletCodeColum = i;
                }
                else if (value.equalsIgnoreCase("巷道号")) {
                    aisleColum = i;
                } else if (value.equalsIgnoreCase("层号")) {
                    layerColum = i;
                } else if (value.equalsIgnoreCase("排号")) {
                    rowColum = i;
                } else if (value.equalsIgnoreCase("列号")) {
                    columnColum = i;
                } else if (value.equalsIgnoreCase("货位状态(0.无货 1.有货)")) {
                    locationInventoryStatusColum = i;
                } else if (value.equalsIgnoreCase("是否禁用(0.禁用 1.启用)")) {
                    statusColum = i;
                }
            }

            if (logicalAddressTypeColum == 0
                    && warehouseCodeColum == 0
                    && storageCodeColum == 0
                    && zoneCodeColum == 0
                    && palletCodeColum == 0
                    && aisleColum == 0
                    && layerColum == 0
                    && rowColum == 0
                    && columnColum == 0
                    && locationInventoryStatusColum == 0
                    && statusColum == 0
            ) {
                return res;
            }

            for (int i = firstRowNum + 1; i <= lastRowNum; i++) {
                Row row = sheet.getRow(i);
                Cell logicalAddressTypeCell = row.getCell(logicalAddressTypeColum);
                Cell warehouseCodeCell = row.getCell(warehouseCodeColum);
                Cell storageCodeCell = row.getCell(storageCodeColum);
                Cell zoneCodeCell = row.getCell(zoneCodeColum);
                Cell palletCodeCell = row.getCell(palletCodeColum);
                Cell aisleCell = row.getCell(aisleColum);
                Cell layerCell = row.getCell(layerColum);
                Cell rowCell = row.getCell(rowColum);
                Cell columnCell = row.getCell(columnColum);
                Cell locationInventoryStatusCell = row.getCell(locationInventoryStatusColum);
                Cell statusCell = row.getCell(statusColum);

                if (logicalAddressTypeCell == null
                        || warehouseCodeCell == null
                        || storageCodeCell == null
                        || zoneCodeCell == null
//                        || palletCodeCell == null
                        || aisleCell == null
                        || layerCell == null
                        || rowCell == null
                        || columnCell == null
                        || locationInventoryStatusCell == null
                        || statusCell == null
                ) {
                    continue;
                }

                int logicalAddressType;
                String warehouseCode;
                String storageCode;
                String zoneCode;
                String palletCode;
                int aisle;
                int layer;
                int rowNum;
                int column;
                int locationInventoryStatus;
                int status;

                if(logicalAddressTypeCell.getCellType() == CellType.STRING){
                    String xx = logicalAddressTypeCell.getStringCellValue();
                    logicalAddressType = Integer.parseInt(xx);
                }else {
                    logicalAddressTypeCell.setCellType(CellType.NUMERIC);
                    logicalAddressType = (int) logicalAddressTypeCell.getNumericCellValue();
                }

                warehouseCodeCell.setCellType(CellType.STRING);
                warehouseCode = warehouseCodeCell.getStringCellValue();

                storageCodeCell.setCellType(CellType.STRING);
                storageCode = storageCodeCell.getStringCellValue();

                zoneCodeCell.setCellType(CellType.STRING);
                zoneCode = zoneCodeCell.getStringCellValue();

                if (palletCodeCell==null){
                    palletCode = null;
                }else {
                      palletCodeCell.setCellType(CellType.STRING);
                     palletCode = palletCodeCell.getStringCellValue();
                }

                if(aisleCell.getCellType() == CellType.STRING){
                    String xx = aisleCell.getStringCellValue();
                    aisle = Integer.parseInt(xx);
                }else {
                    aisleCell.setCellType(CellType.NUMERIC);
                    aisle = (int) aisleCell.getNumericCellValue();
                }

                if(layerCell.getCellType() == CellType.STRING){
                    String xx = layerCell.getStringCellValue();
                    layer = Integer.parseInt(xx);
                }else {
                    layerCell.setCellType(CellType.NUMERIC);
                    layer = (int) layerCell.getNumericCellValue();
                }

                if(rowCell.getCellType() == CellType.STRING){
                    String xx = rowCell.getStringCellValue();
                    rowNum = Integer.parseInt(xx);
                }else {
                    rowCell.setCellType(CellType.NUMERIC);
                    rowNum = (int) rowCell.getNumericCellValue();
                }

                if(columnCell.getCellType() == CellType.STRING){
                    String xx = columnCell.getStringCellValue();
                    column = Integer.parseInt(xx);
                }else {
                    columnCell.setCellType(CellType.NUMERIC);
                    column = (int) columnCell.getNumericCellValue();
                }



                if(locationInventoryStatusCell.getCellType() == CellType.STRING){
                    String xx = locationInventoryStatusCell.getStringCellValue();
                    locationInventoryStatus = Integer.parseInt(xx);
                }else {
                    locationInventoryStatusCell.setCellType(CellType.NUMERIC);
                    locationInventoryStatus = (int) locationInventoryStatusCell.getNumericCellValue();
                }

                if(statusCell.getCellType() == CellType.STRING){
                    String xx = statusCell.getStringCellValue();
                    status = Integer.parseInt(xx);
                }else {
                    statusCell.setCellType(CellType.NUMERIC);
                    status = (int) statusCell.getNumericCellValue();
                }

                PPStorage storageExcel = new PPStorage();

                storageExcel.setLogicalAddressType(logicalAddressType);
                storageExcel.setWarehouseCode(warehouseCode);
                storageExcel.setStorageCode(storageCode);
                storageExcel.setZoneCode(zoneCode);
                storageExcel.setPalletCode(palletCode);
                storageExcel.setAisle(aisle);
                storageExcel.setLayer(layer);
                storageExcel.setRow(rowNum);
                storageExcel.setColumn(column);
                storageExcel.setLocationInventoryStatus(locationInventoryStatus);
                storageExcel.setStatus(status);
                res.add(storageExcel);
            }

            return res;

        } catch (Exception e) {
            log.error("文件读取错误", e);
            return new ArrayList<>();
        }
    }
}
